using System.Data;
using Microsoft.Extensions.Options;
using SPI.AgentServer.Configuration;
using SPI.AgentServer.Services.Execution;

namespace SPI.AgentServer.Services.Tools;

/// <summary>
/// 用户统计工具 - 获取用户统计信息
/// </summary>
public class GetUserStatsTool : IQueryTool
{
    private readonly IDatabaseExecutor _executor;
    private readonly DatabaseConfiguration _config;
    private readonly ILogger<GetUserStatsTool> _logger;

    public string Name => "get_user_stats";
    public string Description => "获取用户统计信息，包括总用户数、在线用户数、按性别统计等";

    public GetUserStatsTool(
        IDatabaseExecutor executor,
        IOptions<DatabaseConfiguration> options,
        ILogger<GetUserStatsTool> logger)
    {
        _executor = executor;
        _config = options.Value;
        _logger = logger;
    }

    public async Task<ToolResult> ExecuteAsync(Dictionary<string, object> parameters, CancellationToken cancellationToken = default)
    {
        if (!ValidateParameters(parameters, out var errors))
        {
            return new ToolResult
            {
                Success = false,
                Errors = errors
            };
        }

        try
        {
            var includeDetails = parameters.ContainsKey("includeDetails")
                ? Convert.ToBoolean(parameters["includeDetails"])
                : false;

            string sql;

            if (includeDetails)
            {
                // 返回详细的用户列表
                sql = @"
SELECT
    Id,
    Name,
    Email,
    Gender,
    CellPhone,
    IsValid,
    CreateTime,
    UpdateTime
FROM frameworkuser
WHERE IsValid = 1
ORDER BY CreateTime DESC";
            }
            else
            {
                // 返回统计摘要
                sql = @"
SELECT
    COUNT(*) as TotalCount,
    SUM(CASE WHEN IsValid = 1 THEN 1 ELSE 0 END) as ActiveCount,
    SUM(CASE WHEN IsValid = 0 THEN 1 ELSE 0 END) as InactiveCount,
    SUM(CASE WHEN Gender = 1 THEN 1 ELSE 0 END) as MaleCount,
    SUM(CASE WHEN Gender = 0 THEN 1 ELSE 0 END) as FemaleCount,
    COUNT(DISTINCT Email) as UniqueEmailCount
FROM frameworkuser";
            }

            _logger.LogInformation("Executing user stats query: {Sql}", sql);

            var result = await _executor.ExecuteQueryAsync(sql, null, cancellationToken);

            return new ToolResult
            {
                Success = true,
                Data = result.Data,
                Message = includeDetails ? $"查询到 {result.RowCount} 个用户" : "用户统计信息查询成功",
                Metadata = new Dictionary<string, object>
                {
                    ["rowCount"] = result.RowCount,
                    ["executionTimeMs"] = result.ExecutionTimeMs,
                    ["executedSql"] = sql
                }
            };
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error executing user stats query");
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { $"查询错误: {ex.Message}" }
            };
        }
    }

    public bool ValidateParameters(Dictionary<string, object> parameters, out List<string> errors)
    {
        errors = new List<string>();
        // 参数都是可选的
        return true;
    }
}
